Present invention embodiments relate to database technology, and more specifically, to improving query cost estimates by estimating zone map effectiveness.
In many data warehouse appliances, the hardware and software resources are balanced so that all components (e.g., storage, networking, CPU and accelerator resources) are busy when performing a large database table scan. The performance of such queries is improved not by optimizing any one component of the data path, but by identifying subsets of the rows with column values that do not need to be scanned. The “zone map” mechanism enables this optimization.
For queries that may make use of zone maps, query plan optimizers conventionally assign a cost for a table scan that assumes all pages of the table will be read. Alternatively, an optimizer may perform a “just in time statistics” scan, evaluating zone maps for a table to discover how many pages will remain in the scan list. However, for queries that end up having very high selectivity (i.e., few of the regions actually need to be scanned), a significant amount of time may be spent generating the “just in time statistics” compared to performing the query itself.